import { NextRequest, NextResponse } from "next/server";
import { SqliteDatabase } from "@/lib/sqlite-database";
import fs from 'fs';
import path from 'path';

export async function POST(request: NextRequest) {
  try {
    const database = new SqliteDatabase();

    // 清空所有基础参数数据
    const clearStmt = database['db'].prepare('DELETE FROM base_parameters');
    clearStmt.run();

    // 获取所有设备信息
    const allDevices = database['db'].prepare('SELECT * FROM devices').all() as any[];

    if (allDevices.length === 0) {
      return NextResponse.json({
        success: false,
        message: "数据库中没有设备数据，请先导入设备数据"
      });
    }

    // 读取Excel基础参数表CSV文件
    const csvFilePath = path.join(process.cwd(), 'excel', '06-21', '属性表-基础参数表.csv');
    if (!fs.existsSync(csvFilePath)) {
      return NextResponse.json({
        success: false,
        message: "Excel基础参数表文件不存在"
      });
    }

    const csvContent = fs.readFileSync(csvFilePath, 'utf-8');
    const lines = csvContent.split('\n').filter(line => line.trim() !== '');

    // 解析CSV数据，按表ID分组
    const parametersByTable = new Map<string, Array<{
      sequence: string;
      name: string;
      example: string;
      type: string;
      measurement: string;
      source: string;
    }>>();

    let currentTableId = '';
    let currentTableDescription = '';

    for (const line of lines) {
      if (line.trim() === '') continue;
      const columns = line.split(',');
      if (columns.length >= 6) {
        const sequence = columns[0]?.trim() || '';
        const name = columns[1]?.trim() || '';
        const example = columns[2]?.trim() || '';
        const type = columns[3]?.trim() || '';
        const measurement = columns[4]?.trim() || '';
        const source = columns[5]?.trim() || '';

        // 检查是否是表标识行（包含表描述信息）
        if (sequence.includes('表 C.') || sequence.includes('表C.')) {
          // 提取表ID，支持多种格式：表 C.14、表C.14、表 C.109-1等
          const tableMatch = sequence.match(/表\s*C\.[\d-]+/);
          if (tableMatch) {
            currentTableId = tableMatch[0].replace(/\s+/g, ''); // 去掉空格统一格式
            currentTableDescription = name || '';
            if (!parametersByTable.has(currentTableId)) {
              parametersByTable.set(currentTableId, []);
            }
          }
          continue;
        }

        // 跳过标题行
        if (sequence === '序号' && name === '中文名称') {
          continue;
        }

        // 如果是有效的参数行，添加到当前表
        if (name && name !== '' && currentTableId && sequence.match(/^\d+$/)) {
          if (!parametersByTable.has(currentTableId)) {
            parametersByTable.set(currentTableId, []);
          }
          parametersByTable.get(currentTableId)!.push({
            sequence,
            name,
            example,
            type,
            measurement,
            source
          });
        }
      }
    }

    console.log(`从Excel文件中解析出 ${parametersByTable.size} 个基础参数表`);
    console.log('可用的表ID:', Array.from(parametersByTable.keys()));

    // 为每个设备添加对应的基础参数
    let totalInserted = 0;
    let matchedDevices = 0;
    let unmatchedDevices = [];
    
    for (const device of allDevices) {
      const baseTableId = device.base_params_table_id;
      if (!baseTableId || baseTableId.trim() === '' || baseTableId === '-') {
        continue;
      }

      let tableParams = null;
      let matchedTableId = '';

      // 首先进行精确匹配
      if (parametersByTable.has(baseTableId)) {
        tableParams = parametersByTable.get(baseTableId);
        matchedTableId = baseTableId;
      }

      // 如果精确匹配失败，进行模糊匹配
      if (!tableParams) {
        // 标准化设备表ID用于比较
        const normalizedDeviceTableId = baseTableId.replace(/\s+/g, '').replace('表', '').replace('C', '').replace('.', '');
        
        for (const [excelTableId, params] of parametersByTable) {
          const normalizedExcelTableId = excelTableId.replace(/\s+/g, '').replace('表', '').replace('C', '').replace('.', '');
          
          // 多种匹配策略
          if (
            normalizedExcelTableId === normalizedDeviceTableId ||
            normalizedExcelTableId.includes(normalizedDeviceTableId) ||
            normalizedDeviceTableId.includes(normalizedExcelTableId) ||
            excelTableId.includes(baseTableId.replace('表 ', '').replace('表', '')) ||
            baseTableId.includes(excelTableId.replace('表 ', '').replace('表', ''))
          ) {
            tableParams = params;
            matchedTableId = excelTableId;
            break;
          }
        }
      }

      if (!tableParams) {
        unmatchedDevices.push({
          device_id: device.device_id,
          name: device.name,
          base_params_table_id: baseTableId
        });
        continue;
      }

      matchedDevices++;
      for (const param of tableParams) {
        try {
          database.insertBaseParameter({
            device_id: device.id!,
            sequence: param.sequence,
            parameter_name: param.name,
            example_description: param.example === '—' ? '' : param.example,
            data_type: param.type,
            measurement_class: param.measurement === '—' ? '' : param.measurement,
            data_source: param.source
          });
          totalInserted++;
        } catch (error) {
          console.error(`插入基础参数失败: 设备ID ${device.id}, 参数 ${param.name}`, error);
        }
      }
    }

    const finalCount = database['db'].prepare('SELECT COUNT(*) as total FROM base_parameters').get() as { total: number };

    return NextResponse.json({
      success: true,
      message: "基础参数数据重新导入成功",
      details: {
        totalDevices: allDevices.length,
        matchedDevices: matchedDevices,
        unmatchedDevices: unmatchedDevices.length,
        unmatchedDevicesList: unmatchedDevices,
        actualInserted: totalInserted,
        databaseCount: finalCount.total,
        availableTables: Array.from(parametersByTable.keys()),
        tableStatistics: Array.from(parametersByTable.entries()).map(([tableId, params]) => ({
          tableId,
          parameterCount: params.length
        }))
      }
    });
  } catch (error) {
    console.error('基础参数导入错误:', error);
    return NextResponse.json({
      success: false,
      message: "导入过程中发生错误",
      error: String(error)
    });
  }
}